package com.css.fxfzaqbz.modules.data.repository.impl;


import com.css.fxfzaqbz.constants.YNEnum;
import com.css.fxfzaqbz.modules.data.entity.HistoricalSeismicData;
import com.css.fxfzaqbz.modules.data.entity.HistoricalSeismicDataDTO;
import com.css.fxfzaqbz.modules.data.queryParams.HistoricalSeismicDataQueryParams;
import com.css.fxfzaqbz.modules.data.repository.HistoricalSeismicDataRepository;
import com.css.fxfzaqbz.util.PlatformObjectUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class HistoricalSeismicDataRepositoryImpl implements HistoricalSeismicDataRepository {

    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

//    @Override
//    @Transactional(rollbackFor = Exception.class)
//    public ActiveFaultData updateActiveFaultData(ActiveFaultData activeFaultData) {
//
//        String sql = "update sys_activefaultdata set afd_number = :afdNumber," +
//                " afd_name = :afdName, afd_length = :afdLength," +
//                " afd_newactivity = :afdNewactivity, afd_status = :afdStatus, " +
//                " afd_updatetime = :afdUpdatetime" +
//                " where afd_id = '"+activeFaultData.getAfdId()+"'" ;
//
//        SqlParameterSource source = new BeanPropertySqlParameterSource(activeFaultData);
//        //拿到新插入的主键值（i）
//        KeyHolder keyHolder = new GeneratedKeyHolder();
//        namedParameterJdbcTemplate.update(sql,source,keyHolder);
//        return activeFaultData;
//    }

    @Override
    @Transactional
    public HistoricalSeismicData saveHistoricalSeismicData(HistoricalSeismicData historicalSeismicData) {
        String sql = "insert into sys_historicalseismicdata values(" +
                ":hsdId,:hsdNumber," +
                ":hsdSeismictime,:hsdLongitude," +
                ":hsdLatitude,:hsdMagnitude," +
                ":hsdDepth,:hsdIntensity," +
                ":hsdPrecision,:hsdCkdm," +
                ":hsdStatus,:hsdCreatetime)";

        SqlParameterSource source = new BeanPropertySqlParameterSource(historicalSeismicData);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return historicalSeismicData;
    }

    @Override
    public Page<HistoricalSeismicData> getAllHistoricalSeismicDataParams(HistoricalSeismicDataQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select  h.* from sys_historicalseismicdata h ");

        StringBuilder orderSql = new StringBuilder(" order by h.hsd_number");

        StringBuilder whereSql = new StringBuilder(" where h.hsd_status = '" + YNEnum.Y.toString() + "'");

//        if (!PlatformObjectUtils.isEmpty(queryParams.getElevationBand()) ){
//            whereSql.append(" and e.elevation_band like '%" + queryParams.getElevationBand() + "%'");
//        }
//
//        if (!PlatformObjectUtils.isEmpty(queryParams.getElevationNodatavalue()) ){
//           whereSql.append(" and e.elevation_nodatavalue like '%" + queryParams.getElevationNodatavalue() + "%'");
//        }

        String wholeSql = sql.append(whereSql).append(orderSql).toString();
        List<HistoricalSeismicData> list = jdbcTemplate.query(wholeSql, new BeanPropertyRowMapper<>(HistoricalSeismicData.class));
        Pageable pageable = PageRequest.of(queryParams.getCurPage() - 1, queryParams.getPageSize());

        Page<HistoricalSeismicData> historicalSeismicData = new PageImpl<>(list, pageable, PlatformObjectUtils.isEmpty(list) ? 0 : list.size());

        return historicalSeismicData;
    }

    @Override
    public HistoricalSeismicData getHistoricalSeismicDataById(String id) {
        String sql = "select * from sys_historicalseismicdata where hsd_id = '"+id+"'  and hsd_status = '"+YNEnum.Y+"'";
        HistoricalSeismicData historicalSeismicData = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(HistoricalSeismicData.class));
        return historicalSeismicData;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void deleteHistoricalSeismicDatasById(List<String> ids, Timestamp timestamp) {
        String sql = "update sys_historicalseismicdata set hsd_status = '" + YNEnum.N + "' , hsd_updatetime = '"+timestamp+"' " + " where hsd_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public List<HistoricalSeismicData> getAll() {
        return jdbcTemplate.query("select * from sys_historicalseismicdata where hsd_status = '"+YNEnum.Y+"'",new BeanPropertyRowMapper<>(HistoricalSeismicData.class));
    }

    @Override
    public void importExcel(List<HistoricalSeismicDataDTO> historicalSeismicData) {
        jdbcTemplate.batchUpdate("insert into sys_historicalseismicdata (" +
                "hsd_id,hsd_number,hsd_seismictime," +
                "hsd_longitude,hsd_latitude,hsd_magnitude," +
                "hsd_depth,hsd_intensity,hsd_precision," +
                "hsd_ckdm,hsd_status,hsd_createtime" +
                ")values(?,?,?,?,?,?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public int getBatchSize() {

                    return historicalSeismicData.size();

            }
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, historicalSeismicData.get(i).getHsdId());
                ps.setString(2,historicalSeismicData.get(i).getHsdNumber());

                Date zfsjtime = new Date(historicalSeismicData.get(i).getTypeTime().getTime()) ;

                ps.setDate(3, zfsjtime);
                ps.setFloat(4,historicalSeismicData.get(i).getHsdLongitude());
                ps.setFloat(5,historicalSeismicData.get(i).getHsdLatitude());
                ps.setFloat(6,historicalSeismicData.get(i).getHsdMagnitude());
                ps.setString(7,historicalSeismicData.get(i).getHsdDepth());
                ps.setString(8,historicalSeismicData.get(i).getHsdIntensity());
                ps.setString(9,historicalSeismicData.get(i).getHsdPrecision());
                ps.setString(10,historicalSeismicData.get(i).getHsdCkdm());
                ps.setString(11,historicalSeismicData.get(i).getHsdStatus());
                Date createtime = new Date(historicalSeismicData.get(i).getHsdCreatetime().getTime()) ;
                ps.setDate(12,createtime);
            }
        });
    }


}
